<?php if(!defined('CALL')) exit('No direct script access allowed');

include_once('lib/mysql.class.php');
include_once('lib/utils.class.php');
include_once('cfg/cfg.php');

class User
{
	private $db = null;

	public $props = null;

	public function __construct()
	{
		global $dburl, $def_encoding;
		MySQL::$url = $dburl;
		MySQL::$encoding = $def_encoding;
		$this->db = MySQL::getInstance();
	}
	
	public function auth()
	{
		// проверка username & password переданных через POST
		if(isset($_POST['email']) && !empty($_POST['password']))
		{
			// Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, 'authorize by username/password');
			if($this->login($_POST['email'], $_POST['password']))
			{
				header('location: '.ROOT_URL);
				return TRUE;
			}
			else
			{
				unset($_SESSION[UID]);
				return FALSE;
			}
		}
		// проверка в сессии user_id
		elseif(empty($_SESSION[UID]))
		// никаких зацепок нет, т.е. посетителя авторизовать невозможно
		{
			return FALSE;
		}
		else
		{
			$this->load_props();
			return TRUE;
		}
	}
	
	public function login($email, $password)
	{
		$enc_password = md5(md5($password).SALT);
		
		$sql = "SELECT id, password FROM users WHERE email = '".$email."' LIMIT 1";
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$res = $this->db->query($sql);
		
		if($this->db->num_rows($res) > 0)
		{
			$row = $this->db->fetch_assoc($res);
			if($row['password'] == $enc_password)
			{
				$this->db->free_result($res);
				
				$_SESSION[UID] = $row['id'];
				return TRUE;
			}
			else
				Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, 'PASSWORD NOT VALID');
			
		}
		else
			Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, 'USER NOT FOND');
			
		$this->db->free_result($res);
		return FALSE;
	}
	
	public function logout()
	{
		unset($_SESSION[UID]);
	}
	
	public function load_props()
	{
		$this->props = array();
		$sql =
			"SELECT u.dt, u.company_id, c.title as company, u.email, u.enabled, u.info ".
			  "FROM users u left join companies c on u.company_id = c.id ".
			 "WHERE u.id = ".$_SESSION[UID];
			/*
			"SELECT u.dt, u.company_id, c.title as company, u.is_super, u.email, u.enabled, u.info ".
			  "FROM users u, companies c ".
			 "WHERE u.company_id = c.id AND u.id = ".$_SESSION[UID];
			*/
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$res = $this->db->query($sql);
		
		if($this->db->num_rows($res) > 0)
		{
			$row = $this->db->fetch_assoc($res);
			Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, print_r($row, true));
			foreach($row as $key=>$val)
			{
				$this->props[$key] = $val;
			}
		}
			
		$this->db->free_result($res);
	}
	
	public function change_password($old, $new, $repeat)
	{
		if($new != $repeat) return 'Новый пароль указан с ошибками, повторите еще раз.';
		
		$sql = "SELECT password FROM users WHERE id = ".$_SESSION[UID];
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$res = $this->db->query($sql);
		
		$row = $this->db->fetch_assoc($res);
		$psw = md5(md5($old).SALT);
		
		$this->db->free_result($res);
		
		if($row['password'] != $psw) return 'Старый пароль указан неверно';
		
		$psw = md5(md5($new).SALT);
		$sql = "UPDATE users SET password = '$psw' WHERE id = ".$_SESSION[UID];
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$res = $this->db->query($sql);
		
		return 'OK';
	}
	
	public function is_super()
	{
		if(empty($_SESSION[UID])) return false;
		
		return empty($this->props["company_id"]);
		//return $this->props["is_super"] == 1;
	}
	
/*****************************************************************************************************/
	public function load_messages()
	{
		$a = array();
		// данные по всем компаниям доступны только суперюзеру
		if($this->is_super())
			$cond_company = "";
		else
			$cond_company = "AND m.company_id = ".$this->props["company_id"]." ";

		// выборка по периоду
		if(empty($_GET['dt_beg']) || empty($_GET['dt_end']))
			$cond_period = "";
		else
			$cond_period = " AND m.dt BETWEEN ".$this->db->escape($_GET['dt_beg'])." AND date_add(".$this->db->escape($_GET['dt_end']).", interval 1 day) ";
		
		$sql = "SELECT ".
				"m.id, ". 								//0
				"m.dt, ".								//1
				"c.title company, ".					//2
				"ifnull(l.phone, '-') as phone, ".		//3
				"ifnull(l.title, '-') as client,  ".	//4
				"ifnull(m.sms, '-') as sms, ".			//5
				"char_length(m.sms) as len, ".			//6
				"m.dt_sent, ".							//7
				"m.msg, ".								//8
				"c.info, ".								//9
				"ifnull(l.ident, '-') as ident, ".		//10
				"ifnull(l.enabled, '-') as send2, ".	//11
				"ifnull(t.title, '-') as template, ".	//12
				"ifnull(t.re_id, '-') as re_id, ".		//13
				"ifnull(t.re_find, '-') as re_find, ".	//14
				"ifnull(t.re_repl, '-') as re_repl, ".	//15
				"m.company_id, ".						//16
				"m.template_id ".						//17
			"FROM msg m ".
			"LEFT JOIN companies c ON c.id = m.company_id ".
			"LEFT JOIN templates t ON t.id = m.template_id ".
			"LEFT JOIN clients l ON l.id = m.client_id ".
			"WHERE 1=1 ".
			$cond_period.
			$cond_company.
			"ORDER BY m.id DESC";

		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		// $sql = "SELECT m.id, m.dt, c.title, m.sms FROM msg m, companies c WHERE c.id = m.company_id".$where;
		$a = $this->query_data($sql);
		return $a;
	}
	
	public function load_message_details($msg_id)
	{
		$a = array();
		if(empty($msg_id)) return $a;
		
		// загружаем шаблон
		$sql = "SELECT r.id, r.title, r.re_find, r.re_repl, t.code, null as re_res ".
			"FROM templates t, msg m ".
			"WHERE m.id = $msg_id AND r.company_id = m.company_id AND t.id = r.type_id";
		/*
		// загружаем все фильтры от компании приславшей письмо
		$sql = "SELECT r.id, r.title, r.re_find, r.re_repl, t.code, null as re_res ".
			"FROM re r, re_types t, msg m ".
			"WHERE m.id = $msg_id AND r.company_id = m.company_id AND t.id = r.type_id";
		*/
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);

		$rows = array();
		$res = $this->db->query($sql);
		while($row = $this->db->fetch_assoc($res))
			array_push($rows, $row);
		$this->db->free_result($res);
		
		// загружаем только отработавшие фильтры
		$sql = "SELECT l.re_id as id, l.re_res, l.msg_id ".
			"FROM msg_re_links l ".
			"WHERE l.msg_id = $msg_id";
		
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$res = $this->db->query($sql);
		while($row = $this->db->fetch_assoc($res))
		{
			for($i = 0; $i < count($rows); $i++)
			{
				// если фильтр отработал
				if($rows[$i]['id'] == $row['id'])
				{
					$rows[$i]['re_res'] = $row['re_res'];
					// выбираем найденных клиентов
					$ids = $this->found_clients($row['msg_id'], $row['id']);
					if($ids)
						$rows[$i]['clients'] = $this->load_clients_by_id($ids);
				}
			}
		}
		$this->db->free_result($res);
		
		$a['cli'] = array();
		$a['sms'] = array();
		foreach($rows as $row)
		{
			if($row['code'] == 'id')
			{
				array_push($a['cli'], $row);
			}
			else
				array_push($a['sms'], $row);
		}
		unset($rows);
		
		return $a;
	}
	
	private function found_clients($msg_id, $re_id)
	{
		// загружаем все фильтры от компании приславшей письмо
		$sql = "SELECT cli_id FROM msg_cli_links WHERE msg_id = $msg_id AND re_id = $re_id";
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);

		$ids = "";
		$res = $this->db->query($sql);
		while($row = $this->db->fetch_assoc($res))
			$ids .= ",".$row["cli_id"];
		$this->db->free_result($res);
		if(strlen($ids) > 0) $ids = substr($ids, 1);
		return $ids;
	}

	
/*****************************************************************************************************/
	private $cmp_fields = array('id', 'title', 'info');
	
	public function load_companies()
	{
		$a = array();
		// данные доступны только суперюзеру
		if($this->is_super())
		{
			$sql = "SELECT id, title, info, dt FROM companies";
			$a = $this->query_data($sql);
		}
		return $a;
	}
	
	public function update_company()
	{
		$col_num = $_POST['columnId'];
		$col_val = $this->db->escape($_POST['value']);
		$id = $_POST['id'];
		$sql = "update companies set ".$this->cmp_fields[$col_num]." = ".$col_val." where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
	}

	public function insert_company()
	{
		$title = $_POST['title'];
		$info = $_POST['info'];
		
		$sql = "insert into companies(title, info) values('$title', '$info')";
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return $this->db->insert_id();
	}

	public function delete_company()
	{
		$id = $_POST['id'];
		$sql = "select id from users where company_id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		$a = $this->query_data($sql);
		if(count($a) > 0) return "Этого партнера удалить нельзя, т.к. существуют связанные с ним данные! $a[0]";
		
		$sql = "delete from companies where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return "ok";
	}
	
	public function get_company_list()
	{
		$a = array();
		// данные доступны только суперюзеру
		if($this->is_super())
		{
			$sql = "SELECT id, title FROM companies";
			$a = $this->dict_data($sql);
		}
		return $a;
	}
	
/*****************************************************************************************************/
	private $usr_fields = array('id', 'email', 'company', 'info');
	
	public function load_users()
	{
		$a = array();
		// данные доступны только суперюзеру
		if($this->is_super())
		{
			$sql = "SELECT u.id, u.email, c.title company, u.info FROM users u, companies c WHERE u.company_id = c.id AND u.company_id IS NOT NULL";
			$a = $this->query_data($sql);
		}
		return $a;
	}
	
	public function update_user()
	{
		$col_num = $_POST['columnId'];
		$col_val = $this->db->escape($_POST['value']);
		$id = $_POST['id'];
		$sql = "update users set ".$this->usr_fields[$col_num]." = ".$col_val." where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
	}

	public function insert_user()
	{
		$email = $_POST['email'];
		$comp_id = $_POST['company_id'];
		$info = $_POST['info'];
		$password = $_POST['password2'];
		$password = md5(md5($password).SALT);
		
		$uid = $_SESSION[UID];

		$sql = "insert into users(email, password, company_id, info) values('$email', '$password', $comp_id, '$info')";
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return $this->db->insert_id();
	}

	public function delete_user()
	{
		$id = $_POST['id'];
		$sql = "delete from users where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return "ok";
	}
	
/*****************************************************************************************************/
	private $cli_fields = array('id', 'ident', 'phone', 'title', 'enabled', 'info', 'dt');
	private $cli_as_str = array(false, true, true, true, false, true, true);	
	
	public function load_clients()
	{
		$sql_fld = "";
		foreach($this->cli_fields as $f)
			$sql_fld .= ', '.$f;
		$sql_fld = substr($sql_fld, 2);
		
		$cond_comp = $this->is_super() ? "" : "and company_id = ".$this->props["company_id"]." ";
		$sql = "SELECT ".$sql_fld." FROM clients WHERE 1=1 ".$cond_comp;
		$a = $this->query_data($sql);
		
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, print_r($a, true));
		return $a;
	}
	
	public function load_clients_by_id($id_list)
	{
		$sql_fld = "";
		foreach($this->cli_fields as $f)
			$sql_fld .= ', '.$f;
		$sql_fld = substr($sql_fld, 2);
		
		$sql = "SELECT ".$sql_fld." FROM clients WHERE id IN ($id_list)";
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, print_r($a, true));

		$a = array();
		$res = $this->db->query($sql);
		while($row = $this->db->fetch_assoc($res))
			array_push($a, $row);
		$this->db->free_result($res);
		
		return $a;
	}
	
	public function update_client()
	{
		$col_num = $_POST['columnId'];
		$col_val = $this->cli_as_str[$col_num] ? "'".$_POST['value']."'" : $_POST['value'];
		$id = $_POST['id'];
		$sql = "update clients set ".$this->cli_fields[$col_num]." = ".$col_val." where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
	}

	public function insert_client()
	{
		$ident = $this->fld_quot('ident', $_POST['ident']);
		$phone = $this->fld_quot('phone', $_POST['phone']);
		$title = $this->fld_quot('title', $_POST['title']);
		$enabled = $this->fld_quot('enabled', $_POST['enabled']);
		$info = $this->fld_quot('info', $_POST['info']);
		
		$uid = $_SESSION[UID];
		$comp_id = $this->props["company_id"];

		$sql = "insert into clients(ident, phone, title, enabled, info, user_id, company_id) ".
			"values($ident, $phone, $title, $enabled, $info, $uid, $comp_id)";

		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return $this->db->insert_id();
	}

	public function delete_client()
	{
		$id = $_POST['id'];
		$sql = "delete from clients where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return "ok";
	}
	
/*****************************************************************************************************/
	private $set_fields = array('id', 'title', 're_find', 're_repl', 'company_id', 'user_id', 'type_id');
	
	public function load_settings($type)
	{
		$a = array();
		if($this->is_super())
		{
			$comp = "";
		}
		else
		{
			$comp = " AND company_id = ".$this->props["company_id"];
		}
		
		$sql = "SELECT id, title, re_find, re_repl, company_id, user_id, type_id FROM re WHERE type_id = $type".$comp;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$a = $this->query_data($sql);
		return $a;
	}

	public function update_settings()
	{
		$col_num = $_POST['columnId'];
		$col_val = $this->db->escape($_POST['value']);
		$id = $_POST['id'];
		$sql = "update re set ".$this->set_fields[$col_num]." = ".$col_val." where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
	}

	public function insert_settings()
	{
		$title = $this->db->escape($_POST['title']);
		$re_find = $this->db->escape($_POST['re_find']);
		$re_repl = $this->db->escape($_POST['re_repl']);
		$type_id = $_POST['type_id'];
		
		$uid = $_SESSION[UID];
		$comp_id = $this->props["company_id"];

		$sql = "insert into re(title, re_find, re_repl, type_id, user_id, company_id) ".
			"values($title, $re_find, $re_repl, $type_id, $uid, $comp_id)";

		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return $this->db->insert_id();
	}

	public function delete_settings()
	{
		$id = $_POST['id'];
		$sql = "delete from re where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return "ok";
	}
	
/* TEMPLATES *****************************************************************************************/
	private $tpl_fields = array('id', 'title', 'pos', 're_id', 're_find', 're_repl', 'sms_it', 'company_id', 'user_id');
	
	public function load_templates()
	{
		$a = array();
		if($this->is_super())
		{
			$comp = "";
		}
		else
		{
			$comp = " AND company_id = ".$this->props["company_id"];
		}
		
		$sql = "SELECT id, title, pos, re_id, re_find, re_repl, sms_it, company_id, user_id FROM templates WHERE 1=1".$comp." ORDER BY pos";
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$a = $this->query_data($sql);
		return $a;
	}

	public function update_template()
	{
		$col_num = $_POST['columnId'];
		$col_val = $this->db->escape($_POST['value']);
		$id = $_POST['id'];
		$sql = "update templates set ".$this->tpl_fields[$col_num]." = ".$col_val." where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
	}

	public function insert_template()
	{
		$title = $this->db->escape($_POST['title']);
		$re_id = $this->db->escape($_POST['re_id']);
		$re_find = $this->db->escape($_POST['re_find']);
		$re_repl = $this->db->escape($_POST['re_repl']);
		$sms_it = $this->db->escape($_POST['sms_it']);
		
		$pos = $_POST['pos'];
		
		$uid = $_SESSION[UID];
		$comp_id = $this->props["company_id"];

		$sql = "insert into templates(title, pos, re_id, re_find, re_repl, sms_it, user_id, company_id) ".
			"values($title, $pos, $re_id, $re_find, $re_repl, $sms_it, $uid, $comp_id)";

		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return $this->db->insert_id();
	}

	public function delete_template()
	{
		$id = $_POST['id'];
		$sql = "delete from templates where id = ".$id;
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		return "ok";
	}
	
/*****************************************************************************************************/
	public function clear_log()
	{
		/*
		$sql = "DELETE FROM msg_cli_links";
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		$sql = "DELETE FROM msg_re_links";
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
		*/
		$sql = "DELETE FROM msg";
		if(LOG_SQL) Logger::Write(__FILE__." ".__FUNCTION__." line: ".__LINE__, $sql);
		$this->db->query($sql);
	}
	
/*****************************************************************************************************/
	private function fld_quot($fld_name, $fld_val)
	{
		for($i = 0; $i < count($this->cli_fields); $i++)
		{
			if($fld_name == $this->cli_fields[$i])
				return $this->cli_as_str[$i] ? "'".$fld_val."'" : $fld_val;
		}	
		return "'".$fld_val."'";
	}
	
	public function load_stat($dt_beg, $dt_end)
	{
		
	}

	private function dict_data($sql)
	{
		$a = array();
		
		$res = $this->db->query($sql);
		while($row = $this->db->fetch_row($res))
			$a[$row[0]] = $row[1];
		$this->db->free_result($res);
		
		return $a;
	}
	
	private function query_data($sql)
	{
		$a = array();
		
		$res = $this->db->query($sql);
		while($row = $this->db->fetch_row($res))
			array_push($a, $row);
		$this->db->free_result($res);
		
		return $a;
	}
}
